package com.teach.dao;


import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import com.sun.org.apache.xerces.internal.impl.dv.xs.DateDV;
import com.teach.entity.user;

import java.sql.Statement;

public class UserDao {
	public static Connection conn = null;
	public static Statement stmt = null;
	public static ResultSet rs = null;
	public static PreparedStatement ptst = null;

  
	public static void add(user u) throws SQLException {
		try {
			Date date=new Date();
			java.sql.Date dd=new java.sql.Date(date.getTime());
			u.setCreate_time(dd);
			conn = JdbcUtil.getConnection();
			String sql = "insert into user(id,name,sex,password,create_time,birthday,nickname,account_number,phone,user_address) values(null,?,?,?,?,?,?,?,?,?) ";
			if (conn == null) {
				return;
			} 
			ptst = conn.prepareStatement(sql);
			ptst.setString(1, u.getName());
			ptst.setString(2, u.getSex());
			ptst.setString(3, u.getPassword());
			ptst.setDate(4, u.getCreate_time());
			ptst.setDate(5, u.getBirthday());
			ptst.setString(6, u.getNickname());
			ptst.setString(7, u.getAccount_number());
			ptst.setString(8, u.getPhone());
			ptst.setString(9, u.getUser_address());
			
			ptst.executeUpdate();
			
			

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtil.close(conn, ptst);
		}
	}

	
	
	public void updateById(user u) throws SQLException {
		try {
			conn = JdbcUtil.getConnection();
			if (conn == null) {
				
				return;
			}
			String sql = "update user set name=?,sex=?,password=?,create_time=?,birthday=?,nickname=?,account_number=?,phone=?,user_address=?  where id = ? ";
			ptst = conn.prepareStatement(sql);
			
			ptst.setString(1, u.getName());
			ptst.setString(2, u.getSex());
			ptst.setString(3, u.getPassword());
			ptst.setDate(4, u.getCreate_time());
			ptst.setDate(5, u.getBirthday());
			ptst.setString(6, u.getNickname());
			ptst.setString(7, u.getAccount_number());
			ptst.setString(8, u.getPhone());
			ptst.setString(9, u.getUser_address());
			ptst.setInt(10, u.getId());
			ptst.executeUpdate();
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtil.close(conn, ptst);
		}

	}
	
	public void deleteById(int id) throws SQLException {
		try {
			conn = JdbcUtil.getConnection();
			
			String sql = "delete from user where id = ?";
			ptst = conn.prepareStatement(sql);
			ptst.setInt(1, id);
			ptst.executeUpdate();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtil.close(conn, ptst);
		}
		

	}
   public List  SelectByString(String s) throws SQLException {
	  
	   List<user> list=new ArrayList<user>();
		try {
			conn = JdbcUtil.getConnection();
			
			String sql = "select * from  user where nickname like '%"+s+"%'";
			ptst = conn.prepareStatement(sql);
			rs = ptst.executeQuery();

			while (rs.next()) {
				user u = new user();
				u.setId(rs.getInt("id"));
				u.setSex(rs.getString("sex"));
				u.setName(rs.getString("name"));
				u.setPassword(rs.getString("password"));
				u.setCreate_time(rs.getDate("create_time"));
				u.setBirthday(rs.getDate("birthday"));
				u.setNickname(rs.getString("nickname"));
				u.setAccount_number(rs.getString("account_number"));
				u.setPhone(rs.getString("phone"));
				u.setUser_address(rs.getString("user_address"));
				
				list.add(u);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			JdbcUtil.close(conn, ptst,rs);
			return list;
		}
	}
   
  public user selectByName(String s) throws SQLException {
	  user u = new user();
		try {
			conn = JdbcUtil.getConnection();
			
			String sql = "select * from  user where name = ?  ";
			ptst = conn.prepareStatement(sql);
			ptst.setString(1, s);
			rs = ptst.executeQuery();
			while (rs.next()) {
				u.setId(rs.getInt("id"));
				u.setSex(rs.getString("sex"));
				u.setName(rs.getString("name"));
				u.setPassword(rs.getString("password"));
				u.setCreate_time(rs.getDate("create_time"));
				u.setBirthday(rs.getDate("birthday"));
				u.setNickname(rs.getString("nickname"));
				u.setAccount_number(rs.getString("account_number"));
				u.setPhone(rs.getString("phone"));
				u.setUser_address(rs.getString("user_address"));
				
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			JdbcUtil.close(conn, ptst,rs);
			return u;
		}
		
	   
   }

	public user selectById(int id) throws SQLException {
		user u = new user();
		try {
			conn = JdbcUtil.getConnection();
			
			String sql = "select * from  user where id = ?  ";
			ptst = conn.prepareStatement(sql);
			ptst.setInt(1, id);
			rs = ptst.executeQuery();
			while (rs.next()) {
				u.setId(rs.getInt("id"));
				u.setSex(rs.getString("sex"));
				u.setName(rs.getString("name"));
				u.setPassword(rs.getString("password"));
				u.setCreate_time(rs.getDate("create_time"));
				u.setBirthday(rs.getDate("birthday"));
				u.setNickname(rs.getString("nickname"));
				u.setAccount_number(rs.getString("account_number"));
				u.setPhone(rs.getString("phone"));
				u.setUser_address(rs.getString("user_address"));
				
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			JdbcUtil.close(conn, ptst,rs);
			return u;
		}
		
		

	}

	public List<user> selectAll() throws SQLException {
		List<user> list = new ArrayList<>();
		try {
			conn = JdbcUtil.getConnection();
			
			String sql = "select * from  user ";
			ptst = conn.prepareStatement(sql);
			rs = ptst.executeQuery();

			while (rs.next()) {
				user u = new user();
				u.setId(rs.getInt("id"));
				u.setSex(rs.getString("sex"));
				u.setName(rs.getString("name"));
				u.setPassword(rs.getString("password"));
				u.setCreate_time(rs.getDate("create_time"));
				u.setBirthday(rs.getDate("birthday"));
				u.setNickname(rs.getString("nickname"));
				u.setAccount_number(rs.getString("account_number"));
				u.setPhone(rs.getString("phone"));
				u.setUser_address(rs.getString("user_address"));
				
				list.add(u);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtil.close(conn, ptst,rs);
			return list;
		}
	}

	public List<user> selectIndex(int from,int end) throws SQLException {
		List<user> list = new ArrayList<>();
		try {
			conn = JdbcUtil.getConnection();
			
			String sql = "select * from  user limit "+from+","+end;
			ptst = conn.prepareStatement(sql);
			rs = ptst.executeQuery();

			while (rs.next()) {
				user u = new user();
				u.setId(rs.getInt("id"));
				u.setSex(rs.getString("sex"));
				u.setName(rs.getString("name"));
				u.setPassword(rs.getString("password"));
				u.setCreate_time(rs.getDate("create_time"));
				u.setBirthday(rs.getDate("birthday"));
				u.setNickname(rs.getString("nickname"));
				u.setAccount_number(rs.getString("account_number"));
				u.setPhone(rs.getString("phone"));
				u.setUser_address(rs.getString("user_address"));
				
				list.add(u);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtil.close(conn, ptst,rs);
			return list;
		}
	}
	
	public int countAll() throws SQLException {

		List<user> list=selectAll();
		
		return list.size();
//		try {
//			
//			if (conn == null) {
//				System.out.println("数据库链接出错");
//				return;
//			}
//			String sql = "select count(*) from  user  ";
//			ptst = conn.prepareStatement(sql);
//			rs = ptst.executeQuery();
//			rs.next();
//
//			
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			JdbcUtil.close(conn, ptst,rs);
//		}
	}

}
